package co.th.bjc.md.helper.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import co.th.bjc.md.dao.MemberDAO;
import co.th.bjc.md.helper.entries.HelperDatabse;
import co.th.bjc.md.helper.utils.Common;

public class MemberHelper extends HelperDatabse {

	private static final String FIND_BY_PK = "SELECT * FROM bjc_md.member WHERE member_code = ?";
	
	public MemberDAO findByPK(int memberCode) throws Exception{
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		MemberDAO member = null;
		try {
			con = getConnection();
			stmt = con.prepareStatement(FIND_BY_PK);
			stmt.setInt(1, memberCode); 

			rs = stmt.executeQuery();
			if (rs.next()){
				member = setMemberDAO(rs);
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (con != null) con.close();
		}
		return member;
	}
	
	public List<MemberDAO> findByCriteria(MemberDAO memberDAO) throws Exception{
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<MemberDAO> listMember = new ArrayList<MemberDAO>(0);
		StringBuffer sql = new StringBuffer();
		try {
			sql.append("SELECT * FROM BJC_MD.MEMBER WHERE 1=1 ");
			if (Common.isNotNull(memberDAO.getMemberCode()))
				sql.append("AND " + MemberDAO.COLUMN.MEMBER_CODE + " = " + memberDAO.getMemberCode() + " ");
			if (Common.isNotNull(memberDAO.getTitle()))
				sql.append("AND " + MemberDAO.COLUMN.TITLE + " = '" + memberDAO.getTitle() + "' ");
			if (Common.isNotNull(memberDAO.getFirstName()))
				sql.append("AND " + MemberDAO.COLUMN.FIRST_NAME + " = '" + memberDAO.getFirstName() + "' ");
			if (Common.isNotNull(memberDAO.getLastName()))
				sql.append("AND " + MemberDAO.COLUMN.LAST_NAME + " = '" + memberDAO.getLastName() + "' ");
			if (Common.isNotNull(memberDAO.getEmail()))
				sql.append("AND " + MemberDAO.COLUMN.EMAIL + " = '" + memberDAO.getEmail() + "' ");
			if (Common.isNotNull(memberDAO.getMoblieNumber()))
				sql.append("AND " + MemberDAO.COLUMN.MOBILE_NUMBER + " = '" + memberDAO.getMoblieNumber() + "' ");
			if (Common.isNotNull(memberDAO.getQuestionForget()))
				sql.append("AND " + MemberDAO.COLUMN.QUESTION_FORGET + " = '" + memberDAO.getQuestionForget() + "' ");
			if (Common.isNotNull(memberDAO.getEmployeeCode()))
				sql.append("AND " + MemberDAO.COLUMN.EMPLOYEE_CODE + " = '" + memberDAO.getEmployeeCode() + "' ");
			if (Common.isNotNull(memberDAO.getCreate_by()))
				sql.append("AND " + MemberDAO.COLUMN.CREATE_BY + " = '" + memberDAO.getCreate_by() + "' ");
			if (Common.isNotNull(memberDAO.getCreate_dtm()))
				sql.append("AND " + MemberDAO.COLUMN.CREATE_DTM + " = '" + memberDAO.getCreate_dtm() + "' ");
			if (Common.isNotNull(memberDAO.getUpdate_by()))
				sql.append("AND " + MemberDAO.COLUMN.UPDATE_BY + " = '" + memberDAO.getUpdate_by() + "' ");
			if (Common.isNotNull(memberDAO.getUpdate_dtm()))
				sql.append("AND " + MemberDAO.COLUMN.UPDATE_DTM + " = '" + memberDAO.getUpdate_dtm() + "' ");

			con = getConnection();
			stmt = con.prepareStatement(sql.toString());

			rs = stmt.executeQuery();
			while (rs.next()){
				listMember.add(setMemberDAO(rs));
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (con != null) con.close();
		}
		return listMember;
	}
	
	private MemberDAO setMemberDAO(ResultSet rs) throws java.sql.SQLException{
		MemberDAO member = new MemberDAO();
		member.setMemberCode(rs.getLong(MemberDAO.COLUMN.MEMBER_CODE));
		member.setTitle(rs.getString(MemberDAO.COLUMN.TITLE));
		member.setFirstName(rs.getString(MemberDAO.COLUMN.FIRST_NAME));
		member.setLastName(rs.getString(MemberDAO.COLUMN.LAST_NAME));
		member.setEmail(rs.getString(MemberDAO.COLUMN.EMAIL));
		member.setMoblieNumber(rs.getString(MemberDAO.COLUMN.MOBILE_NUMBER));
		member.setQuestionForget(rs.getString(MemberDAO.COLUMN.QUESTION_FORGET));
		member.setEmployeeCode(rs.getLong(MemberDAO.COLUMN.EMPLOYEE_CODE));
		member.setCreate_by(rs.getString(MemberDAO.COLUMN.CREATE_BY));
		member.setCreate_dtm(rs.getTimestamp(MemberDAO.COLUMN.CREATE_DTM));
		member.setUpdate_by(rs.getString(MemberDAO.COLUMN.UPDATE_BY));
		member.setUpdate_dtm(rs.getTimestamp(MemberDAO.COLUMN.UPDATE_DTM));
		return member;
	}
}
